import datetime
import re
import sys

sys.path.extend(["F:/ML/BNC", "D:/BNC", "/www/wwwroot/BNC"])

from Utils.Funcc import parse_rp_2_rows
from WebServer.blog.models import DBSession_mysql, CodeTagDate, CodeTagRelation, ENABLE
import operator
from functools import reduce
import pandas as pd


def main():
    # session = DBSession_mysql()
    # SearchCTRKWMapper.search_kw_(session)
    file_name_ = '物联网20200216.xlsm'
    df = pd.read_excel("G:/" + file_name_)
    need_col = {'代码', }
    tag_extract = ""
    m = re.match("(.*?)\d+\.xl.*", file_name_)
    if m:
        tag_extract = "test_" + m[0]
    # 判断头部是否含有充分信息
    if not (set(df.columns) & need_col == need_col):
        raise ValueError("缺少必要的列，当前为:{} 需要:{}".format(df.columns, need_col))

    remove_row_idx = [r_idx_ for r_idx_, row_ in df.iterrows() if
                      not (re.match("\d{6}", df.loc[[r_idx_], '代码'].item()))]
    df = df.drop(remove_row_idx, axis=0, inplace=False)

    res = []
    for r_idx, row_ in df.loc[:, need_col].iterrows():
        ctr_ = CodeTagRelation()
        ctr_.enable = True
        # ctr_.code =
        ctr_.code = row_['代码']
        ctr_.tag = tag_extract
        res.append(ctr_)
    return res
    # res += '\n'
    # print(res)


def parse_excel_2_obj(file):
    file_name_ = file.name
    df = pd.read_excel(file)
    need_col = {'代码', }
    tag_extract = ""
    m = re.match("(.*?)\d+\.xl.*", file_name_)
    if m:
        tag_extract = m[1]
    # 判断头部是否含有充分信息
    if not (set(df.columns) & need_col == need_col):
        raise ValueError("缺少必要的列，当前为:{} 需要:{}".format(df.columns, need_col))

    remove_row_idx = [r_idx_ for r_idx_, row_ in df.iterrows() if
                      not (re.match("\d{6}", df.loc[[r_idx_], '代码'].item()))]
    df = df.drop(remove_row_idx, axis=0, inplace=False)

    res = []
    for r_idx, row_ in df.loc[:, need_col].iterrows():
        ctr_ = CodeTagRelation()
        ctr_.enable = ENABLE
        # ctr_.code =
        ctr_.code = row_['代码']
        setattr(ctr_, "origin", tag_extract)
        ctr_.tag = tag_extract
        ctr_.start_date = datetime.date(2015, 1, 1)
        ctr_.reason = '批量插入'
        ctr_.source = '通达信'

        res.append(ctr_)
    return res


class SearchCTRKWMapper:
    @classmethod
    def search_kw_(cls, session):
        combine_res_ = []
        rp = session.execute("SELECT DISTINCT TR.to from tag_rename AS TR;")
        combine_res_.extend(parse_rp_2_rows(rp))
        combine_res_ = reduce(operator.add, combine_res_)

        rp = session.execute("SELECT DISTINCT code_tag_relation.code from code_tag_relation;")
        rp = reduce(operator.add, parse_rp_2_rows(rp))
        combine_res_.extend(["{}(code)".format(c_) for c_ in rp])
        print(combine_res_)
        return combine_res_


if __name__ == '__main__':
    main()
